package com.and9.mchen.mcmusic.dao.impl;

import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.and9.mchen.mcmusic.constant.RelationTable;
import com.and9.mchen.mcmusic.dao.IBaseEntityDao;
import com.and9.mchen.mcmusic.dao.ISingerDao;
import com.and9.mchen.mcmusic.entity.Album;
import com.and9.mchen.mcmusic.entity.Introduction;
import com.and9.mchen.mcmusic.entity.MusicStyle;
import com.and9.mchen.mcmusic.entity.Singer;
import com.and9.mchen.mcmusic.exception.DBException;
import com.and9.mchen.mcmusic.exception.DaoException;
import com.and9.mchen.mcmusic.util.EntityHelper;
import com.and9.mchen.mcmusic.util.GEntityHelper;
import com.and9.mchen.mcmusic.util.QueryHelper;

public class SingerDaoImpl implements ISingerDao{

	private final static Log log = LogFactory.getLog(MusicStyleDaoImpl.class);

	public static String TABLEMANE="singer_table";
	
	public int createOrUpdateSinger(Singer singer, String... propertyNames) throws DaoException {
	
		if(GEntityHelper.isEmpty(singer)){
			return -1;
		}
		
		String sqlStr=null;
		Object[] propertyValues=null;
		
		if(singer.getId()==null || singer.getId() <=0){
			if(GEntityHelper.isEmpty(singer.getUniqueId())){
				singer.setUniqueId(EntityHelper.createUniqueId());
			}
			singer.setCreateTime(new Timestamp(System.currentTimeMillis()));
			Object[] parentParams=QueryHelper.getFieldsVal(singer.getParent(), IBaseEntityDao.BASEPROPERTYNAMES);
			Object[] singerParams=QueryHelper.getFieldsVal(singer, ISingerDao.BASEPROPERTYNAMES);
			propertyValues=EntityHelper.groupArrays(parentParams, singerParams);
			
			propertyNames=EntityHelper.groupArrays(IBaseEntityDao.BASEPROPERTYNAMES, ISingerDao.BASEPROPERTYNAMES);
			sqlStr=QueryHelper.getInsertSQLByProperty(SingerDaoImpl.TABLEMANE, propertyNames);
			
		}else{
			
			if(singer.getId().equals(1L)){
				return 0;
			}
			
			if(GEntityHelper.isEmpty(propertyNames) || 
					EntityHelper.containBaseEntityPropertyName(propertyNames) == true){
				propertyNames=ISingerDao.BASEPROPERTYNAMES;
			}
			
			propertyValues=QueryHelper.getFieldsVal(singer, propertyNames);
			
			sqlStr=QueryHelper.getUpdateSQL(SongDaoImpl.TABLEMANE, singer, "id="+singer.getId(), propertyNames);
			
		}
		
		try {
			return QueryHelper.update(sqlStr, propertyValues);
		} catch (DBException e) {
			log.error("[function: createOrUpdateSinger]", e);
			throw new DaoException("create or update singer faliure");
		}
	}
	
	public int createSingerImageUrlRelevanceBySinger(Singer singer)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer) || 
			!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer.getImageUrl())){
			return -1;
		}
		
		if(singer.getId().equals(1L)){
			return 0;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SingerDaoImpl.TABLEMANE, "id="+singer.getId(), 
				"image_url_id");
		
		try {
			return QueryHelper.update(sqlStr, singer.getImageUrl().getId());
		} catch (DBException e) {
			log.error("[function: createSingerImageUrlRelevanceBySinger]", e);
			throw new DaoException("create singer imageUrl relevance by singer failure");
		}
		
	}

	public int[] createSingerMusicStyleRelevanceBySinger(Singer singer)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer) || 
				GEntityHelper.isEmpty(singer.getMusicStyles())){
			return null;
		}
		
		if(singer.getId().equals(1L)){
			return null;
		}
		
		Set<MusicStyle> musicStyles=(Set<MusicStyle>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(singer.getMusicStyles());
		
		if(GEntityHelper.isEmpty(musicStyles)){
			return null;
		}
		
		Object[][] params =EntityHelper.initRelevanceParamsById(singer, musicStyles);
		
		String sqlStr=QueryHelper.getInsertSQLByProperty(RelationTable.SINGER_MUSIC_STYLE_TABLE, "singer_id", "music_style_id");
		
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: createSingerMusicStyleBySinger]", e);
			throw new DaoException("create singer musicStyle relevance by singer failure");
		}
	}
	
	public int deleteSingerBySingerId(long id) throws DaoException {

		if(id <= 0){
			return -1;
		}
		
		if(id == 1){
			return 0;
		}
		
		String sqlStr=QueryHelper.getDeleteSQL(SingerDaoImpl.TABLEMANE, "id=?");

		try {
			return QueryHelper.update(sqlStr, id);
		} catch (DBException e) {
			log.error("[function: deleteSingerBySingerId]", e);
			throw new DaoException("delete singer by id failure");
		}

	}

	public Singer getSingerByAlbumId(long id) throws DaoException {
		
		if(id <= 0){
			return null;
		}
		
		String sqlStr=MessageFormat.format("SELECT DISTINCT {0}.* FROM {0}, {1} " +
				"WHERE {0}.id={1}.singer_id AND {1}.singer_id > 1 AND {1}.id=?", SingerDaoImpl.TABLEMANE, 
					AlbumDaoImpl.TABLEMANE);
		
		try {
			Singer singer=QueryHelper.read(Singer.class, sqlStr, id);
			
			return singer != null && singer.getId() > 1 ? singer : null;
			
		} catch (DBException e) {
			log.error("[function: getSingerByAlbumId]", e);
			throw new DaoException("get singers by album id failure");
		}
	
	}
	
	
	public Singer getSingerBySingerId(long id) throws DaoException {
		
		if( id <= 1){
			return null;
		}
		
		try {
			String sqlStr=QueryHelper.getSelectSQL(SingerDaoImpl.TABLEMANE, "id=?");
			return QueryHelper.read(Singer.class, sqlStr, id);
		} catch (DBException e) {
			log.error("[function: getSingerBySingerId]", e);
			throw new DaoException("get singer by id failure");
		}
	}
	
	public Singer getSingerBySingerUnqiueId(String uniqueId)
			throws DaoException {
		
		if(GEntityHelper.isEmpty(uniqueId)){
			return null;
		}
		
		String sqlStr=QueryHelper.getSelectSQL(SingerDaoImpl.TABLEMANE, "uniqueId=?");
		
		try {
			Singer singer=QueryHelper.read(Singer.class, sqlStr, uniqueId);
			return singer != null && singer.getId() > 1 ? singer : null ;
		} catch (DBException e) {
			log.error("[function: getSingerBySingerUnqiueId]", e);
			throw new DaoException("get singer by singer unqiueId failure");
		}
	}
	
	public List<Singer> getSingersByMusciStyleId(long id, int pageNum, int pageCount, 
			String orderBy) throws DaoException {
		
		if(id <= 0){
			return null;
		}
		
		if(GEntityHelper.isEmpty(orderBy)){
			orderBy=MessageFormat.format("{0}.isStick DESC, {0}.clickCount DESC ", 
					StatisticsDaoImpl.TABLEMANE);
		}
		
		String sqlStr=MessageFormat.format("SELECT DISTINCT {0}.* FROM {0}, {1}, {2} WHERE " +
				"{0}.id={1}.singer_id AND {0}.id>1 AND {0}.statistics_id={2}.id AND {1}.music_style_id=? ORDER BY {3}", 
					SingerDaoImpl.TABLEMANE, RelationTable.SINGER_MUSIC_STYLE_TABLE, 
						StatisticsDaoImpl.TABLEMANE, orderBy);
		
		try {
			return QueryHelper.query_slice(Singer.class, sqlStr, pageNum, pageCount, id);
		} catch (DBException e) {
			log.error("[function: getSingersByMusciStyleId]", e);
			throw new DaoException("get singers by musci style id failure");
		}

	}

	public List<Singer> getSingersBySongId(long id) throws DaoException {
		
		if(id <= 0){
			return null;
		}
		
		String sqlStr=MessageFormat.format("SELECT {0}.* FROM {0}, {1} WHERE " +
				"{0}.id = {1}.singer_id AND {0}.id > 1 AND {1}.id IN ( SELECT DISTINCT {1}.id FROM " +
					"{1}, {2} WHERE {1}.id={2}.album_id AND {2}.song_id=?)", SingerDaoImpl.TABLEMANE,
						AlbumDaoImpl.TABLEMANE, RelationTable.ALBUM_SONG_TABLE);
		
		try {
			return QueryHelper.query(Singer.class, sqlStr, id);
		} catch (DBException e) {
			log.error("[function: getSingersBySongId]", e);
			throw new DaoException("get singers by song id failure");
		}
	
	}

	public long getSingersCountByMusciStyleId(long id) throws DaoException {
		
		if(id <= 0){
			return -1;
		}
		
		String sqlStr=MessageFormat.format("SELECT COUNT({0}.id) FROM {0}, {1} WHERE " +
				"{0}.id={1}.singer_id AND {0}.id > 1 AND {1}.music_style_id=?", SingerDaoImpl.TABLEMANE, 
					RelationTable.SINGER_MUSIC_STYLE_TABLE);
		
		try {
			return QueryHelper.stat(sqlStr, id);
		} catch (DBException e) {
			log.error("[function: getSingersCountByMusciStyleId]", e);
			throw new DaoException("get singers count by musci style id failure");
		}
		
	}

	public int removeSingerImageUrlRelevanceBySinger(Singer singer)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer) ||
			!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer.getImageUrl())){
			return -1;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SingerDaoImpl.TABLEMANE, "id="+singer.getId(), "image_url_id");
		
		try {
			return QueryHelper.update(sqlStr, 1L);
		} catch (DBException e) {
			log.error("[function: removeSingerImageUrlRelevanceBySinger]", e);
			throw new DaoException("remove singer imageUrl relevance by singer failure");
		}
		
	}

	public int[] removeSingerMusicStyleRelevanceBySinger(Singer singer)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer) || 
			GEntityHelper.isEmpty(singer.getMusicStyles())){
			return null;
		}
		
		Set<MusicStyle> musicStyles=(Set<MusicStyle>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(singer.getMusicStyles());
		
		if(GEntityHelper.isEmpty(musicStyles)){
			return null;
		}
		
		Object[][] params =EntityHelper.initRelevanceParamsById(singer, musicStyles);
		
		String sqlStr=QueryHelper.getDeleteSQL(RelationTable.SINGER_MUSIC_STYLE_TABLE, 
				"singer_id=? AND music_style_id=?");
		
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: deleteSingerMusicStyleBySinger]", e);
			throw new DaoException("delete singer musicStyle relevance by singer failure");
		}
	}

	public int createSingerStatisticsRelevanceBySinger(Singer singer)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer) || 
				!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer.getStatistics())){
			return -1;
		}
		
		if(singer.getId().equals(1L)){
			return 0;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SingerDaoImpl.TABLEMANE, "id="+singer.getId(), "statistics_id");
		
		try {
			return QueryHelper.update(sqlStr, singer.getStatistics().getId());
		} catch (DBException e) {
			log.error("[function: createSingerStatisticsRelevanceBySinger]", e);
			throw new DaoException("create singer statistics relevance by singer failure");
		}
	}

	public int removeSingerStatisticsRelevanceBySinger(Singer singer)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer)){
			return -1;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SingerDaoImpl.TABLEMANE, "id="+singer.getId(), "statistics_id");
		
		try {
			return QueryHelper.update(sqlStr, 1);
		} catch (DBException e) {
			log.error("[function: removeSingerStatisticsRelevanceBySinger]", e);
			throw new DaoException("remove singer statistics relevance by singer failure");
		}
	}

	public int createSingerIntroductionRelevanceBySinger(Singer singer)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer) || 
				!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer.getIntroduction())){
			return -1;
		}
		
		if(singer.getId().equals(1L)){
			return 0;
		}
		
		Introduction introduction=singer.getIntroduction();
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SingerDaoImpl.TABLEMANE, "id=?", "introduction_id");
		
		try {
			//UPDATE  song_table   SET  statistics_id =? where id=?
			return QueryHelper.update(sqlStr, introduction.getId(), singer.getId());
		} catch (DBException e) {
			log.error("[function: createSingerIntroductionRelevanceBySinger]", e);
			throw new DaoException("create singer introduction relevance by singer failure");
		}
	}

	public int removeSingerIntroductionRelevanceBySinger(Singer singer)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer)){
			return -1;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SongDaoImpl.TABLEMANE, "id=?", "introduction_id");
		
		try {
			
			return QueryHelper.update(sqlStr, 1L, singer.getId());
		} catch (DBException e) {
			log.error("[function: removeSingerIntroductionRelevanceBySinger]", e);
			throw new DaoException("remove singer introduction relevance by singer failure");
		}
	}

	public int[] createSingerAlbumRelevanceBySinger(Singer singer)
			throws DaoException {

		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer) || 
				GEntityHelper.isEmpty(singer.getAlbums())){
			return null;
		}
		
		if(singer.getId().equals(1L)){
			return null;
		}
		
		Set<Album> albums=(Set<Album>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(singer.getAlbums());
		
		if(GEntityHelper.isEmpty(albums)){
			return null;
		}
		
		Object[][] params =EntityHelper.initRelevanceParamsById(singer, albums);
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(AlbumDaoImpl.TABLEMANE, "id=? ", "singer_id");
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: createSingerAlbumRelevanceBySinger]", e);
			throw new DaoException("create singer album relevance by singer failure");
		}
		
	}

	public int[] removeSingerAlbumRelevanceBySinger(Singer singer)
			throws DaoException {

		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(singer) || 
				GEntityHelper.isEmpty(singer.getAlbums())){
			return null;
		}
		
		Set<Album> albums=(Set<Album>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(singer.getAlbums());
		
		if(GEntityHelper.isEmpty(albums)){
			return null;
		}
		
		Object[][] params=new Object[albums.size()][2];
		
		int i=0;
		
		Iterator<Album> iterator=albums.iterator();
		
		while(iterator.hasNext()){
			params[i][0]=1L;
			params[i][1]=iterator.next().getId();
			i++;
		}
		
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(AlbumDaoImpl.TABLEMANE, "id=? ", "singer_id");
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: removeSingerAlbumRelevanceBySinger]", e);
			throw new DaoException("remove singer album relevance by singer failure");
		}
		
	}
	
}
